package Postgre;

import org.testng.annotations.AfterClass;
import org.testng.annotations.BeforeClass;
import org.testng.annotations.Test;

import java.sql.*;

import static org.testng.Assert.assertEquals;



/**
 * Created by IntelliJ IDEA.
 * User: Andrey
 * Date: 16.03.12
 * Time: 20:47
 * To change this template use File | Settings | File Templates.
 */
public class testGetForeignKey {
    private Connection con;

    @BeforeClass
    public void connect() throws ClassNotFoundException, SQLException {
        Class.forName("org.postgresql.Driver");
        con = DriverManager.getConnection("jdbc:postgresql://localhost:5432/test", "andrey", "andrey");

        Statement st = con.createStatement();
        st.executeUpdate("CREATE TABLE testconstraints (\n" +
                "  someid integer NOT NULL,\n" +
                "  somename character varying(10) NOT NULL,\n" +
                "  CONSTRAINT testconstraints_id_pk PRIMARY KEY (someid)\n" +
                ")");
        st.executeUpdate("CREATE TABLE testconstraints2 (\n" +
                "  ext_id integer NOT NULL,\n" +
                "  modified date,\n" +
                "  uniquefield character varying(10) NOT NULL,\n" +
                "  usraction integer NOT NULL,\n" +
                "  CONSTRAINT testconstraints_id_fk FOREIGN KEY (ext_id)\n" +
                "      REFERENCES testconstraints (someid) MATCH SIMPLE\n" +
                "      ON UPDATE CASCADE ON DELETE CASCADE,\n" +
                "  CONSTRAINT unique_2_fields_idx UNIQUE (modified, usraction),\n" +
                "  CONSTRAINT uniquefld_idx UNIQUE (uniquefield)\n" +
                ")");
    }

    @AfterClass
    public void closeConnection() throws SQLException {
        Statement st = con.createStatement();
        st.execute("drop table testconstraints2");
        st.execute("drop table testconstraints");

        con.close();
    }

    @Test
    public void testGetTables() throws SQLException {
        String fKey;
        Statement st = con.createStatement();

        ResultSet rs = st.executeQuery("SELECT c.conname\n" +
                " FROM pg_constraint c, pg_class t\n" +
                "    WHERE t.relname = 'testconstraints2'\n" +
                "     AND c.contype = 'f';");

        rs.next();
        fKey = rs.getString("conname");
        System.err.println("Primary key: " + fKey);
        assertEquals(fKey, "testconstraints_id_fk");
    }
}

